OpenEdge Data Management:<br />SQL Development Working with tables

Release 10.1A: OpenEdge Data Management:
SQL Development

Working with tables

The following sections provide details on creating, modifying, and deleting tables.

Using the CREATE TABLE statement

The CREATE TABLE statement allows you to create a new table in an existing database by defining its column names and column data types. Optionally, you can include table and column constraints.

The CREATE TABLE statement uses the following syntax:
Syntax
CREATE TABLE [ owner_name.]table_name 
  ( { column_definition | table_constraint }, ...) 
    [ AREA area_name ]  
    ; 
CREATE TABLE [ owner_name.]table_name 
    [ (column_name [ NOT NULL] , ... ) ] 
    [ AREA area_name ] 
    AS query_expression 
    ; 
Example 5–1 illustrates a CREATE TABLE statement. The cust_no column has the column constraint NOT NULL, which indicates that no row in the customer table is to have a NULL value in the cust_no column.
Example 5–1: CREATE TABLE statement
CREATE TABLE SPORTS.Customer 
     ( 
     cust_no INTEGER NOT NULL, 
     last_name CHAR (30), 
     street CHAR (30), 
     city CHAR (20), 
     state CHAR (2) 
     ) ;  
The CREATE TABLE statement also allows you to specify the DEFAULT clause along with a column definition. The DEFAULT clause identifies the default value to be used for a column.

The default clause uses the following syntax:
Syntax
column_name   data_type 
  [ DEFAULT { literal | NULL | SYSDATE | SYSTIME | SYSTIMESTAMP } ] 
  [ column_constraint [ column_constraint , ... ] ] 
The following CREATE TABLE statement shows how to use the DEFAULT clause. Example 5–2 sets a default value of 10 for the deptno column.
Example 5–2: CREATE TABLE statement with DEFAULT clause
CREATE TABLE employee 
     ( 
     empno   INTEGER NOT NULL, 
     deptno  INTEGER DEFAULT 10 
     ) ; 
For more information on the CREATE TABLE statement and the DEFAULT clause, see OpenEdge Data Management: SQL Reference .

ALTER TABLE

The ALTER TABLE statement lets you add new columns to a table, delete columns from a table, or change the format and labels associated with an existing column.
Note: For a complete description of the ALTER TABLE syntax, see OpenEdge Data Management: SQL Reference .

The ALTER TABLE statement has the following syntax:
Syntax
ALTER TABLE [ owner_name.]table_name  
ADD column-definition 
ALTER column_name [ SET DEFAULT value | DROP DEFAULT ] 
DROP column_name { CASCADE | RESTRICT } 
ADD { primary_key_definition | foreign_key_definition | 
uniqueness_constraint | check_constraint } 
DROP CONSTRAINT column_name [ CASCADE | RESTRICT] 
ALTER INDEX index_name 
; 
The addition or deletion of columns is a common modification for tables. When a column is added, the OpenEdge RDBMS places the column to the far right of the table. Unless you declare the column to be NOT NULL and assign a default value, the RDBMS will assume the column has a value of NULL for each row in the existing table.

Example 5–3 shows how the ALTER TABLE statement is used to add a column to a table.
Example 5–3: ALTER TABLE statement
ALTER TABLE SPORTS.Customer 
ADD Customer_phone CHAR (10); 
The ALTER TABLE statement also can be used to change the name of an existing table. To do so, SQL uses the following syntax:
Syntax
ALTER TABLE [owner_name.]table_name RENAME TO [owner_name.]table_name ; 
Example 5–4 shows how the ALTER TABLE statement is used to rename an existing table.
Example 5–4: Using ALTER TABLE statement to rename table
ALTER TABLE SPORTS.employee RENAME TO SPORTS.staff; 
DROP TABLE

The DROP TABLE statement deletes all data and indexes for a table and erases its entry in the system catalog. The DROP TABLE statement uses the following syntax:
Syntax
DROP TABLE  [ owner_name.]table_name ; 
Example 5–5 illustrates the use of a DROP TABLE statement.
Example 5–5: DROP TABLE statement
 DROP TABLE SPORTS.staff; 